//数据库连接
 
const sqlite3 = require('sqlite3')
 const NODE_ENV = process.env.NODE_ENV
 const path = require('path')
 const { app } = require('electron')
 let DB_PATH = path.join(app.getAppPath(), '/dataBase/xczg_ad.db');
 
console.log('连接数据库路径：',app.getAppPath());
console.log('连接数据库路径：',DB_PATH);
 
//判断是否是正式环境
if (app.isPackaged) {
    // 正式环境
    DB_PATH = path.join(path.dirname(app.getPath('exe')), '/dataBase/xczg_ad.db');
}
 
// //连接数据库
function connectDatabase() {
    return new sqlite3.Database('/dataBase/xczg_ad.db', (err) => {
        if (err) {
            console.error('连接数据库错误：' + err.message);

        }
        console.log('连接数据库成功')
    });
}
 const db = connectDatabase();
 
//创建数据库,如果用户本地没有数据库的话就创建否则跳过
function createDataTable() {
    //创建用户表
    db.serialize(function () {
        const sql="create table if not exists patrol_task  (id INTEGER PRIMARY KEY,task_name text(255), task_desc text(255),start_time text,end_time text,dept_id integer,template_id integer,task_backup1 text(255),task_backup2 text(255),task_backup3 text(255),task_backup4 text(255),create_by text(64),create_time text,update_by text(64),update_time text, remark text(500),parent_id integer,ancestors text(50),temaplate_top text(255),task_dept text(255),top_taskId integer ,temaplate_fankui text );"
       // +"create table if not exists patrol_task  (id INTEGER PRIMARY KEY,task_name text(255), task_desc text(255),start_time text,end_time text,dept_id integer,template_id integer,task_backup1 text(255),task_backup2 text(255),task_backup3 text(255),task_backup4 text(255),create_by text(64),create_time text,update_by text(64),update_time text, remark text(500),parent_id integer,ancestors text(50),temaplate_top text(255),task_dept text(255),top_taskId integer ,temaplate_fankui text );"
       const sql1= "create table if not exists patrol_task_dept  (round_id INTEGER , dept_id INTEGER ,task_id INTEGER , task_status integer,is_sign integer,is_overdue integer , is_feedback integer , review_status integer ,is_complete text(255),temaplate_backup1 text(255), temaplate_backup2 text(255), temaplate_backup3 text(255), temaplate_backup4 text(255), create_by text(64),update_by text(64),update_time text, remark text(500),id INTEGER  ,primary key(id,round_id, dept_id ,task_id)); "
       const sql2= "create table if not exists patrol_round  (id INTEGER PRIMARY KEY, task_name text(255), task_desc text(255),temaplate_type text(255),parent_id integer, ancestors text(500), temaplate_top  text(255), temaplate_task text ,start_time text ,deptIds text, temaplate_backup1 text(255), temaplate_backup2 text(255), temaplate_backup3 text(255), temaplate_backup4 text(255), create_by text(64),update_by text(64),update_time text, remark text(500)); "
       const sql3= "create table if not exists patrol_task_fankui  (id INTEGER PRIMARY KEY,fankui_content text, fankui_time text(255), fankui_file text, fankui_person text(255), fankui_name text(255) , task_id INTEGER , dept_id INTEGER, temaplate_backup1 text(255), temaplate_backup2 text(255), temaplate_backup3 text(255), temaplate_backup4 text(255), create_by text(64),update_by text(64),update_time text, remark text(500)); "
       const sql4= "create table if not exists patrol_task_matter  (  assess_zhibiao text(255), zhibiao_score INTEGER, assess_content  text(255), content_score INTEGER, zhanfen INTEGER,score_reduction text(1000),score_standard text(255),score_question text(500), total_score INTEGER, task_id INTEGER ,dept_id INTEGER,temaplate_backup1 text(255), temaplate_backup2 text(255), temaplate_backup3 text(255), temaplate_backup4 text(255), create_by text(64),update_by text(64),update_time text, remark text(500), id INTEGER PRIMARY KEY); "
       const sql5= "create table if not exists sys_dept  ( parent_id INTEGER,ancestors  text(50) ,dept_name  text(30),order_num INTEGER,leader text(20),phone text(11),email text(50),status text(1),del_flag text(1),create_by text(64),update_by text(64),update_time text,  dept_id INTEGER PRIMARY KEY);"; 
       console.log(sql);
        db.run(sql);
        db.run(sql1);
        db.run(sql2);
        db.run(sql3);
        db.run(sql4);
        db.run(sql5);
   
    });
    // db.close();
} 



function run(sql, params = []) {
   
    return new Promise((resolve, reject) => {
        db.run(sql, params, function(err) {
            if (err) {
                reject(err);
            } else { 
                resolve({ id:this.lastID});
            }
        });
    });
}

function get(sql, params = []) {
    return new Promise((resolve, reject) => {
        db.get(sql, params, function(err, row) {
            if (err) {
                console.error('Error running SQL:', sql);
                console.error(err.message);
                reject(err);
            } else {
                resolve(row);
            }
        });
    });
}

function getAll(sql, params = []) {
    return new Promise((resolve, reject) => {
        db.all(sql, params, function(err, rows) {
            if (err) {
                console.error('Error running SQL:', sql);
                console.error(err.message);
                reject(err);
            } else {
                resolve(rows);
            }
        });
    });
}

function close() {
    return new Promise((resolve, reject) => {
        db.close((err) => {
            if (err) {
                console.error(err.message);
                reject(err);
            } else {
                resolve();
            }
        });
    });
}

exports.run = run;
exports.connectDatabase = connectDatabase;
exports.createDataTable = createDataTable;
exports.db = db;